﻿using Core.DataAccess.ApiJson.SearchService;
using Core.DataAccess.ApiJson.SearchServiceProviders;
using Core.Framework;
using Newtonsoft.Json.Linq;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace Core.DataAccess.ApiJson.Controllers
{
    public class BaseSchema
    {
        protected readonly SqlSugarClient sqlSugarClient;
        protected readonly ISearchProvider searchProvider;

        public BaseSchema(SqlSugarClient sqlSugarClient, ISearchProvider searchProvider)
        {
            this.sqlSugarClient = sqlSugarClient;
            this.searchProvider = searchProvider;
        }

        // 同步数据库结构
        protected void DataBaseStructuralSynchronization(Dictionary<string, TableSchema> tableSchemas)
        {
            // 处理表字段
            foreach (var table in tableSchemas)
            {

                // 表名
                string tableName = table.Key;

                if (table.Value.Columns?.Count == 0)
                {
                    throw new Exception($"{table.Key} not columns");
                }

                // 修改表名称
                var tupleTableName = GetNewTableName(table.Key);
                tableName = tupleTableName.Item2;

                var dbColumnInfos = sqlSugarClient.DbMaintenance.GetColumnInfosByTableName(tableName, false);

                // 构造所有数据列
                var columns = table.Value.Columns.Select(item => new DbColumnInfo
                {
                    DataType = item.Value.Type.ToType(),
                    DbColumnName = item.Key,
                    IsNullable = !item.Value.IsNotNull,
                    IsPrimarykey = item.Value.PrimaryKey,
                    IsIdentity = item.Value.Identity,
                    Length = item.Value.GetLength(),
                    DecimalDigits = item.Value.Digits,
                    DefaultValue = item.Value.GetDefault()
                }).ToList();

                Dictionary<string, string> NotNullFieldValue = new Dictionary<string, string>();

                // 检查数据库是否存在
                if (!sqlSugarClient.DbMaintenance.IsAnyTable(tableName, false))
                {
                    // 创建表
                    sqlSugarClient.DbMaintenance.CreateTable(tableName, columns, columns.Any(a => a.IsPrimarykey));
                    InsertData(tableName, table.Value.DefaultData);
                    table.Value.DefaultData = default;

                    foreach (var item in columns)
                    {
                        if (item?.IsNullable == false && item.DbColumnName.IndexOf(">") == -1 && !item.IsPrimarykey || !string.IsNullOrEmpty(item.DefaultValue))
                        {
                            item.IsNullable = true;
                            NotNullFieldValue.Add(item.DbColumnName, item.DefaultValue);
                        }
                    }

                }
                else
                {
                    table.Value.DefaultData = default;

                    var sugarColumns = sqlSugarClient.DbMaintenance.GetColumnInfosByTableName(tableName, false);

                    // Compare 表结构
                    foreach (var item in columns)
                    {
                        var column = sugarColumns.Where(col => col.DbColumnName == item.DbColumnName).FirstOrDefault();

                        if (column?.IsNullable == false && item.DbColumnName.IndexOf(">") == -1 && !item.IsPrimarykey || !string.IsNullOrEmpty(item.DefaultValue))
                        {
                            item.IsNullable = column?.IsNullable == true;
                            NotNullFieldValue.Add(item.DbColumnName, item.DefaultValue);
                        }

                        if (column != null)
                        {
                            // 检查是否需要修改
                            if (item.IsChange(column))
                            {
                                // 修改表结构
                                sqlSugarClient.DbMaintenance.UpdateColumn(tableName, item);
                            }
                        }
                        else
                        {
                            // 创建col 排除改名
                            if (item.DbColumnName.IndexOf(">") == -1)
                            {
                                sqlSugarClient.DbMaintenance.AddColumn(tableName, item);
                            }

                        }
                    }

                }


                // 处理改名字段
                foreach (var column in columns)
                {
                    if (column.DbColumnName.IndexOf(">") >= 0)
                    {
                        var colNames = column.DbColumnName.Split('>');
                        if (colNames.Length > 1)
                        {
                            string oldName = colNames[colNames.Length - 2].Trim();
                            string newName = colNames[colNames.Length - 1].Trim();
                            column.DbColumnName = newName;

                            if (!column.IsNullable)
                            {
                                NotNullFieldValue.Add(newName, column.DefaultValue);
                            }

                            if (sqlSugarClient.DbMaintenance.IsAnyColumn(tableName, oldName, false))
                            {
                                sqlSugarClient.DbMaintenance.RenameColumn(tableName, oldName, newName);
                                // 更新一下
                                sqlSugarClient.DbMaintenance.UpdateColumn(tableName, column);
                            }
                            else
                            {
                                // 创建字段
                                sqlSugarClient.DbMaintenance.AddColumn(tableName, column);
                            }
                        }
                    }
                }

                // 处理默认值
                foreach (var dic in NotNullFieldValue)
                {
                    if (!string.IsNullOrEmpty(dic.Value))
                    {
                        // 默认值 包含中文
                        if (Regex.IsMatch(dic.Value, @"^[\u4e00-\u9fa5]+$"))
                        {
                            sqlSugarClient.Ado.ExecuteCommand($"update [{tableName}] set [{dic.Key}] = N'{dic.Value}' where [{dic.Key}] is null");
                        }
                        else
                        {
                            sqlSugarClient.Ado.ExecuteCommand($"update [{tableName}] set [{dic.Key}] = '{(dic.Value == "getdate()" ? DateTime.Now.ToString() : dic.Value)}' where [{dic.Key}] is null");
                        }

                    }
                }

                // 处理字段默认值属性
                foreach (var dic in NotNullFieldValue)
                {
                    // 获取数据库字段信息
                    var dbColumn = dbColumnInfos.Where(a => a.DbColumnName == dic.Key).FirstOrDefault();

                    var dbColumnNew = columns.Where(a => a.DbColumnName == dic.Key).FirstOrDefault();

                    // 已经是非空就忽略
                    if (dbColumn?.IsNullable == true && dbColumnNew.IsNullable != true)
                    {
                        // 更新字段信息
                        var column = columns.Where(a => a.DbColumnName == dic.Key).First();
                        column.IsNullable = false;
                        sqlSugarClient.DbMaintenance.UpdateColumn(tableName, column);
                    }

                    // 设置默认属性(Value != null)
                    if (!sqlSugarClient.DbMaintenance.IsAnyDefaultValue(tableName, dic.Key) && !string.IsNullOrWhiteSpace(dic.Value))
                    {
                        // 默认值 包含中文
                        if (Regex.IsMatch(dic.Value, @"^[\u4e00-\u9fa5]+$"))
                        {
                            sqlSugarClient.DbMaintenance.AddDefaultValue(tableName, dic.Key, $"N'{dic.Value}'");
                        }
                        else
                        {
                            sqlSugarClient.DbMaintenance.AddDefaultValue(tableName, dic.Key, dic.Value);
                        }
                    }
                }

                // 获取最新的表结构数据
                dbColumnInfos = sqlSugarClient.DbMaintenance.GetColumnInfosByTableName(tableName, false);

                // 处理删除字段
                foreach (var item in dbColumnInfos)
                {
                    if (!columns.Any(a => a.DbColumnName == item.DbColumnName))
                    {
                        // 删除指定列
                        sqlSugarClient.DbMaintenance.DropColumn(tableName, item.DbColumnName);
                    }
                }

                // 处理添加数据
                if (!table.Value.ClearData)
                {
                    InsertData(tableName, table.Value.InsertData);
                }
                else
                {
                    // 删除所有数据
                    sqlSugarClient.Ado.ExecuteCommand($"TRUNCATE TABLE [{tableName}]");
                }


            }

            // 处理删除表
            var tables = sqlSugarClient.DbMaintenance.GetTableInfoList(false);
            var tableNames = tableSchemas.Select(item => GetNewTableName(item.Key).Item2).ToList();
            foreach (var table in tables)
            {
                if (!tableNames.Any(a => a == table.Name) && sqlSugarClient.DbMaintenance.IsAnyTable(table.Name))
                {
                    sqlSugarClient.DbMaintenance.DropTable(table.Name);
                }
            }
        }

        // 初始化数据
        protected void InsertData(string tableName, List<JObject> data)
        {

            if (data?.Count > 0)
            {


                var dbColumnInfos = sqlSugarClient.DbMaintenance.GetColumnInfosByTableName(tableName, false);
                var columns = dbColumnInfos.Select(a => a.DbColumnName).ToList();

                var havPrimarykey = dbColumnInfos.Any(a => a.IsIdentity);

                var fields = string.Join("],[", columns);

                StringBuilder sb = new StringBuilder();

                if (havPrimarykey)
                {
                    sb.AppendLine($"SET IDENTITY_INSERT [{tableName}] ON");
                }

                foreach (var item in data)
                {
                    var values = columns.Select(c => $"N'{(item[c] == null ? "null" : item[c].ToString().Replace("'", "''"))}'");

                    sb.AppendLine(@$"INSERT INTO [{tableName}]([{fields}]) VALUES({string.Join(",", values)})");
                }

                if (havPrimarykey)
                {
                    sb.AppendLine($"SET IDENTITY_INSERT [{tableName}] OFF");
                }


                sqlSugarClient.Ado.ExecuteCommand(sb.ToString());

            }

        }

        // 获取新表名称
        protected Tuple<bool, string> GetNewTableName(string nameString)
        {
            string oldName, newName = nameString;
            bool isRename = false;

            if (nameString.IndexOf(">") >= 0)
            {
                var tableNames = nameString.Split('>');
                if (tableNames.Length > 1)
                {
                    oldName = tableNames[tableNames.Length - 2].Trim();
                    newName = tableNames[tableNames.Length - 1].Trim();

                    if (sqlSugarClient.DbMaintenance.IsAnyTable(oldName, false))
                    {
                        sqlSugarClient.DbMaintenance.RenameTable(oldName, newName);
                    }
                    isRename = true;
                }
                else
                {
                    newName = tableNames[0].Trim();
                }
            }

            return new Tuple<bool, string>(isRename, newName);
        }

        protected Dictionary<string, Column> GetColumnsByTable(string tableName)
        {
            Dictionary<string, Column> keyValues = new Dictionary<string, Column>();
            var columns = sqlSugarClient.DbMaintenance.GetColumnInfosByTableName(tableName, false);

            foreach (var item in columns)
            {
                var column = new Column
                {
                    Identity = item.IsIdentity,
                    Default = item.DefaultValue,
                    Digits = item.DecimalDigits,
                    IsNotNull = !item.IsNullable,
                    Length = item.Length,
                    PrimaryKey = item.IsPrimarykey,
                    Type = ColumnType.text
                };

                string defaultValue = string.Empty;

                if (!string.IsNullOrWhiteSpace(item.DefaultValue))
                {
                    item.DefaultValue = item.DefaultValue.Replace("(N'", "");
                    item.DefaultValue = item.DefaultValue.Replace("((", "");
                    item.DefaultValue = item.DefaultValue.Replace("')", "");
                    item.DefaultValue = item.DefaultValue.Replace("))", "");
                    defaultValue = item.DefaultValue;
                }


                switch (item.DataType)
                {
                    case "int":
                    case "bigint":
                        column.Type = ColumnType.number;
                        column.Length = 0;
                        column.Digits = 0;
                        int.TryParse(defaultValue, out int value1);
                        if (!string.IsNullOrWhiteSpace(item.DefaultValue))
                        {
                            column.Default = value1;
                        }
                        break;

                    case "char":
                        column.Type = ColumnType.@char;
                        column.Digits = 0;
                        break;

                    case "bit":
                        column.Type = ColumnType.@bool;
                        column.Length = 0;
                        column.Digits = 0;
                        if (!string.IsNullOrWhiteSpace(defaultValue))
                        {
                            column.Default = defaultValue.IndexOf("1") > -1 ? 1 : 0;
                        }
                        break;

                    case "float":
                        column.Type = ColumnType.@float;
                        column.Length = 0;
                        column.Digits = 0;
                        float.TryParse(defaultValue, out float value2);
                        if (!string.IsNullOrWhiteSpace(item.DefaultValue))
                        {
                            column.Default = value2;
                        }
                        break;
                    case "decimal":
                        column.Type = ColumnType.@decimal;
                        decimal.TryParse(defaultValue, out decimal value3);
                        if (!string.IsNullOrWhiteSpace(item.DefaultValue))
                        {
                            column.Default = value3;
                        }
                        break;
                    case "datetime":
                        column.Type = ColumnType.date;
                        column.Length = 0;
                        column.Digits = 0;
                        if (!string.IsNullOrWhiteSpace(defaultValue))
                        {
                            column.Default = "now";
                        }
                        break;
                    case "varchar":
                    case "nvarchar":
                    default:
                        column.Type = ColumnType.text;
                        column.Digits = 0;
                        if (!string.IsNullOrWhiteSpace(item.DefaultValue))
                        {
                            column.Default = defaultValue;
                        }
                        break;
                }

                keyValues.Add(item.DbColumnName, column);
            }

            return keyValues;
        }


        protected void IniSearchIndex(Dictionary<string, TableSchema> schema, Dictionary<string, TableSchema> oldSchema)
        {

            foreach (var item in schema)
            {
                string tableName = item.Key;
                string primarykey = sqlSugarClient
                        .DbMaintenance.GetColumnInfosByTableName(tableName)
                        .Where(it => it.IsPrimarykey)
                        .FirstOrDefault()?.DbColumnName;

                if (string.IsNullOrWhiteSpace(primarykey))
                {
                    // 没有主键的表则不创建索引直接跳出
                    break;
                }

                var oldIndexs = oldSchema.Where(a => a.Key == item.Key).Select(a => a.Value.SearchIndex).FirstOrDefault();

                (bool IsSuccess, string Result) result;
                bool isUpdateIndexData = false;
                bool isCreateIndex = false;

                if ((oldIndexs == null || oldIndexs?.Count == 0) && item.Value?.SearchIndex?.Count > 0)
                {
                    // 删除已经存在索引
                    if (searchProvider.AnyIndex(item.Key).IsSuccess)
                    {
                        result = searchProvider.DeleteIndex(item.Key);
                        if (!result.IsSuccess)
                        {
                            throw new Exception($"Delete Index {item.Key} error. {result.Result}");
                        }
                    }

                    //  创建索引 {{item.Value.SearchIndex}}
                    result = searchProvider.CreateIndex(item.Key, this.GetCreateIndexParameters(item, item.Value.SearchIndex));

                    if (!result.IsSuccess)
                    {
                        throw new Exception($"Create Index {item.Key} error. {result.Result}");
                    }

                    isCreateIndex = true;
                }
                else if (item.Value.SearchIndex != null)
                {
                    foreach (var itemIndex in item.Value.SearchIndex)
                    {
                        // 检查当前索引是否是需要创建
                        isCreateIndex = !oldIndexs.ContainsKey(itemIndex.Key);
                        if (!isCreateIndex)
                        {
                            var oldValue = oldIndexs[itemIndex.Key];
                            isUpdateIndexData = itemIndex.Value.Count != oldValue.Count;
                            if (!isUpdateIndexData)
                            {
                                foreach (var itemIndexValue in itemIndex.Value)
                                {
                                    isUpdateIndexData = !oldValue.Contains(itemIndexValue);
                                    if (isUpdateIndexData)
                                    {
                                        break; // 如果存在需要修改则跳出当前循环
                                    }
                                }
                            }
                        }
                    }

                    // 修改索引Mapping
                    if (isCreateIndex || isUpdateIndexData)
                    {
                        if (searchProvider.AnyIndex(item.Key).IsSuccess)
                        {
                            result = searchProvider.ModifyIndex($"{item.Key}/_mappings", this.GetCreateIndexParameters(item, item.Value.SearchIndex).mappings);
                            if (!result.IsSuccess)
                            {
                                throw new Exception($"Modify Index {item.Key} error. {result.Result}");
                            }
                        }
                    }
                }

                // 需要修改索引数据
                if (isCreateIndex || isUpdateIndexData)
                {
                    // (添加/修改)索引数据
                    // TODU  这里应该从数据库获取数据（每次200条）， 直到同步完成

                    int pageIndex = 1;
                    int pageSize = 200;

                    var list = new List<IndexData>();
                    while (this.GetNextPageData(tableName, item.Value, primarykey, pageIndex, pageSize, out list))
                    {
                        result = searchProvider.BlukAdditionIndexDatas(tableName, list);
                        if (!result.IsSuccess)
                        {
                            throw new Exception($"BlukAdd Index {tableName} error. {result.Result}");
                        }

                        if (list.Count < pageSize)
                        {
                            return;
                        }

                        pageIndex++;
                    }
                }



                // 新的有 老的没有   是  add
                // 新的没有 老的有   是  remove （暂时不删除）
                // 新的有 老的有  >  内容是否发生修改？ >   有就是修改，没有就是不动
            }
        }



        private CreateIndexParameters GetCreateIndexParameters(
            KeyValuePair<string, TableSchema> schema,
            Dictionary<string, List<string>> searchIndex)
        {
            var createIndexParameters = new CreateIndexParameters();
            createIndexParameters.mappings = new IndexProperties();
            createIndexParameters.mappings.properties = new Dictionary<string, PropertieType>();

            foreach (var item in searchIndex)
            {
                var fields = item.Value ?? new List<string>();

                // 检查有没有字段
                if (fields.Count < 1)
                {
                    break;
                }

                // 如果大于2个字段  则使用 text 数据类型
                string type = fields.Count > 1 ? "text" : string.Empty;

                // 如果不是text类型
                if (string.IsNullOrWhiteSpace(type))
                {
                    string field = fields[0];
                    if (schema.Value.Columns.ContainsKey(field))
                    {
                        // 根据数据字段赋值
                        var column = schema.Value.Columns[field];
                        switch (column.Type)
                        {

                            case ColumnType.number:
                                type = "integer";
                                break;
                            case ColumnType.@decimal:
                                type = "double";
                                break;
                            case ColumnType.@float:
                                type = "float";
                                break;
                            case ColumnType.date:
                                type = "date";
                                break;
                            case ColumnType.location:
                                type = "geo_point";
                                break;
                            case ColumnType.@bool:
                                type = "boolean";
                                break;
                            case ColumnType.text:
                            case ColumnType.@char:
                            default:
                                type = "text";
                                break;
                        }
                    }
                    else
                    {
                        // 没有找到字段则跳出
                        break;
                    }
                }

                createIndexParameters.mappings.properties.Add(item.Key, new PropertieType(type));
            }

            return createIndexParameters;
        }


        private bool GetNextPageData(string tableName, TableSchema tableSchema, string primarykey, int pageIndex, int pageSize, out List<IndexData> datas)
        {
            datas = new List<IndexData>();
            var dataReader = sqlSugarClient.Ado.GetDataReader(@$"SELECT TOP {pageSize} * FROM 
                                            (
                                                SELECT TOP {pageIndex * pageSize} ROW_NUMBER() OVER (ORDER BY {primarykey}) AS RowNum, * FROM [{tableName}]
                                            ) AS tempTable
                                            WHERE RowNum BETWEEN {(pageIndex - 1) * pageSize + 1} AND {pageIndex * pageSize}
                                            ORDER BY RowNum");

            //var data = sqlSugarClient.Queryable("NotifyMessage", string.Empty).ToPageList(1, 200);
            var indexSchema = tableSchema.SearchIndex;

            while (dataReader.Read())
            {
                Dictionary<string, object> data = new Dictionary<string, object>();

                foreach (var item in indexSchema)
                {
                    bool isText = item.Value.Count > 1;
                    object value = null;
                    string field = item.Value[0];

                    if (isText)
                    {
                        value = string.Join(',', item.Value.Select(a => dataReader[a]).Where(a => a != null));
                    }
                    else if (item.Value.Count == 1)
                    {
                        if (tableSchema.Columns.ContainsKey(field))
                        {
                            switch (tableSchema.Columns[field].Type)
                            {

                                //case ColumnType.number:
                                //    value = Convert.ToInt64(dataReader[field] ?? "0");
                                //    break;
                                //case ColumnType.@decimal:
                                //    value = Convert.ToDecimal(dataReader[field] ?? "0");
                                //    break;
                                //case ColumnType.@float:
                                //    value = Convert.ToDouble(dataReader[field] ?? "0");
                                //    break;

                                case ColumnType.number:
                                case ColumnType.@decimal:
                                case ColumnType.@float:
                                    value = dataReader[field];
                                    break;

                                case ColumnType.date:
                                    value = Convert.ToDateTime(dataReader[field] ?? "1994/03/12 00:00:00");
                                    break;

                                case ColumnType.location:
                                    var locationstring = dataReader[field].ToString();
                                    if (!string.IsNullOrWhiteSpace(locationstring))
                                    {
                                        value = locationstring.TryToEntity<Location>();
                                    }
                                    break;

                                case ColumnType.@bool:
                                    value = Convert.ToBoolean(dataReader[field] ?? "False");
                                    break;

                                case ColumnType.text:
                                case ColumnType.@char:
                                default:
                                    value = dataReader[field].ToString();
                                    break;
                            }
                        }
                    }

                    if (value != null)
                    {
                        data.Add(item.Key, value);
                    }
                    else if (tableSchema.Columns[field].Type == ColumnType.location)
                    {
                        // 俄罗斯地址
                        data.Add(item.Key, new Location { lat = 73.51156F, lon = 100.942979F });
                    }
                }

                datas.Add(new IndexData
                {
                    Key = dataReader[primarykey].ToString(),
                    Data = data
                });
            }

            return datas.Count != 0;
        }

    }
}
